﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using WooYang.Data.Attributes;

namespace WooYang.DataBase
{
    /// <summary>
    /// WooYang
    /// </summary>
    public class SqlBuildHelper
    {
        public SqlBuildHelper()
        {
            
        }
        protected static bool Enabled(PropertyDescriptor property)
        {
            if (property.IsReadOnly)
                return false;
            var codeType = Type.GetTypeCode(property.PropertyType);
            if (property.PropertyType.IsGenericType && typeof(Nullable<>) == property.PropertyType.GetGenericTypeDefinition())
            {
                codeType = Type.GetTypeCode(property.PropertyType.GetGenericArguments()[0]);
            }
            switch (codeType)
            {
                case TypeCode.Empty:
                case TypeCode.Object:
                case TypeCode.DBNull:
                    return false;
                default:
                    return true;
            }
        }
        public static DbCommand GetByIdBuild(Type type, object id, DbProviderFactory factory)
        {
            return GetByIdBuild(type, type.Name, id, factory);
        }
        public static DbCommand GetByIdBuild(Type type, string tableName, object id, DbProviderFactory factory)
        {
            string strSql = "select * from [{0}] where 1=1 {1}";
            StringBuilder field = new StringBuilder();
            var parameters = new List<DbParameter>();
            foreach (System.ComponentModel.PropertyDescriptor property in TypeDescriptor.GetProperties(type))
            {
                    var s = property.Attributes[typeof(KeyAttribute)];
                    if (s != null&&Enabled(property))
                    {
                        string name = "[" + property.Name + "]";
                        var k = property.Attributes[typeof(DBMapAttribute)];
                        if (k != null)
                            name = "[" + k.ToString() + "]";
                        object value = id;
                       
                        field.Append(" and ");
                        field.Append(name + "=@" + property.Name);
                        var parameter = factory.CreateParameter();
                        parameter.ParameterName = property.Name;
                        parameter.Value = value;
                        parameters.Add(parameter);
                        break;
                    }
   
                

            }
            strSql = string.Format(strSql, tableName, field.ToString());
            DbCommand dbCommand = factory.CreateCommand();
            dbCommand.CommandText = strSql;
            dbCommand.Parameters.AddRange(parameters.ToArray());
            return dbCommand;
        }
        public static DbCommand UpdateBuild(object item, DbProviderFactory factory)
        {
            return UpdateBuild(item, item.GetType().Name, factory);
        }
        public static DbCommand UpdateBuild(object item, string tableName, DbProviderFactory factory)
        {
           
            string strSql = "update [{0}] set {1} where 1=1 {2}";
            StringBuilder field = new StringBuilder();
            StringBuilder whr = new StringBuilder();
            var parameters = new List<DbParameter>();
            Type type = item.GetType();
            foreach (System.ComponentModel.PropertyDescriptor property in TypeDescriptor.GetProperties(type))
            {
                if (property.IsReadOnly)
                {
                    continue;
                }
                var s = property.Attributes[typeof(KeyAttribute)];
                if (s == null)
                {
                    if ( Enabled(property))
                    {
                        string name = "[" + property.Name + "]";
                        var k = property.Attributes[typeof(DBMapAttribute)];
                        if (k != null)
                            name = "[" + k.ToString() + "]";
                        object value = property.GetValue(item);
                        if (value != null)
                        {
                            field.Append(name+"=@" + property.Name);
                            field.Append(",");
                            var parameter = factory.CreateParameter();
                            parameter.ParameterName = property.Name;
                            parameter.Value = value;
                            parameters.Add(parameter);
                        }
                    }
                   
                }
                else
                {
                    object value = property.GetValue(item);
                    string name = "[" + property.Name + "]";
                    var k = property.Attributes[typeof(DBMapAttribute)];
                    if (k != null)
                        name = "[" + k.ToString() + "]";
                    if (value != null)
                    {
                        whr.Append(" and ");
                        whr.Append(name + "=@" + property.Name);
                        var parameter = factory.CreateParameter();
                        parameter.ParameterName = property.Name;
                        parameter.Value = value;
                        parameters.Add(parameter);
                    }
                }
            }
            field.Remove(field.Length - 1, 1);
           // whr.Remove(whr.Length - 1, 1);
            strSql = string.Format(strSql, tableName, field.ToString(), whr.ToString());
            DbCommand dbCommand = factory.CreateCommand();
            dbCommand.CommandText = strSql;
            dbCommand.Parameters.AddRange(parameters.ToArray());
            return dbCommand;

        }
        public static DbCommand InsertBuild(object item, DbProviderFactory factory)
        {
            Type type = item.GetType();
            string tableNam = type.Name;
            var k = type.GetCustomAttributes(false).Where(x=>x.GetType()==typeof(DBMapAttribute)).FirstOrDefault();
            if (k != null)
                tableNam = k.ToString();
             return InsertBuild(item, type.Name, factory);
        }
        public static DbCommand InsertBuild(object item, string tableName, DbProviderFactory factory)
        {
            
            string strSql="insert into [{0}] ({1}) values({2});";
            StringBuilder field = new StringBuilder();
            StringBuilder param = new StringBuilder();
            var parameters = new List<DbParameter>();
            Type type = item.GetType();
            foreach (System.ComponentModel.PropertyDescriptor property in TypeDescriptor.GetProperties(type))
            {
                if (property.IsReadOnly)
                {
                    continue;
                }
                var s = property.Attributes[typeof(KeyAttribute)];
               

                if (s == null&Enabled(property))
                {
                     string name = "[" + property.Name + "]";
                    var k = property.Attributes[typeof(DBMapAttribute)];
                    if(k!=null)
                        name = "[" + k.ToString() + "]";
                    object value = property.GetValue(item);
                   
                    if (value != null)
                    {
                        field.Append(name);
                        field.Append(",");
                        param.Append("@" + property.Name);
                        param.Append(",");
                        var parameter = factory.CreateParameter();
                        parameter.ParameterName = property.Name;
                        parameter.Value = value;
                        parameters.Add(parameter);
                    }
                }
            }
            parameters.Capacity = parameters.Count;
            field.Remove(field.Length - 1, 1);
            param.Remove(param.Length - 1, 1);
            strSql = string.Format(strSql,tableName, field.ToString(), param.ToString());
            DbCommand dbCommand = factory.CreateCommand();
            dbCommand.CommandText = strSql;
            dbCommand.Parameters.AddRange(parameters.ToArray());
            return dbCommand;
        }
        public static DbCommand DeleteBuild(object item, DbProviderFactory factory)
        {
            return DeleteBuild(item, item.GetType().Name, factory);
        }
        public static DbCommand DeleteBuild(object item, string tableName, DbProviderFactory factory)
        {
           
            string strSql = "delete [{0}] where 1=1 {1}";
            StringBuilder field = new StringBuilder();
            StringBuilder whr = new StringBuilder();
            var parameters = new List<DbParameter>();
            Type type = item.GetType();
            foreach (System.ComponentModel.PropertyDescriptor property in TypeDescriptor.GetProperties(type))
            {
                object value = property.GetValue(item);
                string name = "[" + property.Name + "]";
                var k = property.Attributes[typeof(DBMapAttribute)];
                if (k != null)
                    name = "[" + k.ToString() + "]";
                if (value != null&&Enabled(property))
                {
                    whr.Append(" and ");
                    whr.Append(name + "=@" + property.Name);
                    var parameter = factory.CreateParameter();
                    parameter.ParameterName = property.Name;
                    parameter.Value = value;
                    parameters.Add(parameter);
                }
            }
            strSql = string.Format(strSql, tableName, whr.ToString());
            DbCommand dbCommand = factory.CreateCommand();
            dbCommand.CommandText = strSql;
            dbCommand.Parameters.AddRange(parameters.ToArray());
            return dbCommand;

        }
        public static DbCommand SelectBuild(string strSql, IEnumerable<DbParameter> parameters, DbProviderFactory factory)  
        {
            DbCommand dbCommand = factory.CreateCommand();
            dbCommand.CommandText = strSql;
            if(parameters!=null)
            dbCommand.Parameters.Add(parameters);
            return dbCommand;
        }

        private static IEnumerable<DbParameter> BuildParameters(object parameterObject, ref string sql, DbProviderFactory factory)
        {
            var parameters = new List<DbParameter>();
            if (parameterObject == null)
                return parameters;

            var dict = parameterObject as IDictionary;
            if (dict != null)
            {
                foreach (object key in dict.Keys)
                {
                    object value = dict[key];
                    string parameterName = key.ToString();
                    BuildParameter(parameterName, value, parameters, ref sql,factory);
                }
                return parameters;
            }
            Type type = parameterObject.GetType();
            foreach (System.ComponentModel.PropertyDescriptor property in TypeDescriptor.GetProperties(type))
            {
                BuildParameter(property.Name, property.GetValue(parameterObject), parameters, ref sql,factory);
            }
            return parameters;
        }

        private static void BuildParameter(string parameterName, object value, List<DbParameter> parameters, ref string sql, DbProviderFactory factory)
        {
            var items = value as IEnumerable;
            if (items != null && items.GetType() != typeof(string) && items.GetType() != typeof(byte[]))
            {
                value = null;
                var names = new List<string>();
                int index = 1;
                foreach (object obj in items)
                {
                    string tmpName = parameterName + "_" + index;
                    names.Add(tmpName);
                    var p = factory.CreateParameter();
                    p.ParameterName = tmpName;
                    p.Value = value;
                    parameters.Add(p);
                    index++;
                    value = true;
                }
                if (value != null)
                {
                    var regex = new Regex(@"\(\s*@" + parameterName + @"\s*\)", RegexOptions.IgnoreCase);
                    sql = regex.Replace(sql, @"(" + string.Join(",", names.Select(x => "@" + x).ToArray()) + ")");
                }
                else // value = null, no items in here
                {
                    var regex = new Regex(@"\(\s*@" + parameterName + @"\s*\)", RegexOptions.IgnoreCase);
                    sql = regex.Replace(sql, @"(null)");
                    value = true;
                }
            }
            var pp = factory.CreateParameter();
            pp.Value = value;
            pp.ParameterName = parameterName;
            parameters.Add(pp);
        }
      
    }
}
